﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using SYcwpb.Helpers;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using SYcwpb.Models;
using SYcwpb.ViewModels;

namespace SYcwpb.Access
{
    class CrossingRoadAccess
    {
        SqlDataLayer sqlHelper = new SqlDataLayer();

        /// <summary>
        /// 获取早班的出库车
        /// </summary>
        /// <returns></returns>
        public ObservableCollection<string> GetMorningShiftTrain(string lastId,string changeZb,string tableName)
        {
            if (string.IsNullOrEmpty(lastId) || string.IsNullOrEmpty(changeZb) || string.IsNullOrEmpty(tableName))
            {
                return null;
            }
            int int_time = Convert.ToInt32(changeZb);

            ObservableCollection<string> List = new ObservableCollection<string>();
            string sql_getMorningTrain = "select distinct TrainID from [" + tableName + "] where (Station = 66 or Station = 67) and LastID ='" + lastId + "' and Depart < " + int_time; 
            DataTable result = sqlHelper.SelectDt(sql_getMorningTrain);
            if (null != result || result.Rows.Count > 0)
            {
                for (int i = 0; i < result.Rows.Count; i++)
                {
                    List.Add(result.Rows[i]["TrainID"].ToString());
                }
            }
            return List;
        }
        public DataTable GetCrossingRoadById(string id) {
            string crSql = "select * from CrossingRoad where ID=" + id;
            DataTable dt = sqlHelper.SelectDt(crSql);
            return dt;
        }
        //public ObservableCollection<string> GetDayTrains(string changeZb, string changeBy, string tableName)
        //{
        //    if (string.IsNullOrEmpty(changeZb) || string.IsNullOrEmpty(changeBy) || string.IsNullOrEmpty(tableName))
        //    {
        //        return null;
        //    }
        //    int int_time_begin = Convert.ToInt32(changeZb);
        //    int int_time_end = Convert.ToInt32(changeBy);

        //    ObservableCollection<string> List = new ObservableCollection<string>();
        //    string sql_getDayTrain = "";
        //    DataTable result = sqlHelper.SelectDt(sql_getDayTrain);
        //    if (null != result || result.Rows.Count > 0)
        //    {
        //        for (int i = 0; i < result.Rows.Count; i++)
        //        {
        //            List.Add(result.Rows[i]["TrainID"].ToString());
        //        }
        //    }
        //    List.Insert(0, "");

        //    return List;
        //}

        /// <summary>
        /// 获取夜班回库车次
        /// </summary>
        /// <param name="changeBy">白班、夜班交接班时间</param>
        /// <param name="?"></param>
        /// <returns></returns>
        public ObservableCollection<string> GetNightBackTrain(string changeBy, string tableName)
        {
            if (string.IsNullOrEmpty(changeBy) || string.IsNullOrEmpty(tableName))
            {
                return null;
            }
            int int_time = Convert.ToInt32(changeBy);

            ObservableCollection<string> List = new ObservableCollection<string>();
            string sql_getNightBackTrain = "select distinct TrainID,* from [" + tableName + "] where arc_ne = '#####' and Arrive > " + changeBy + " order by MidID ";
            DataTable result = sqlHelper.SelectDt(sql_getNightBackTrain);
            if (null != result || result.Rows.Count > 0)
            {
                for (int i = 0; i < result.Rows.Count; i++)
                {
                    List.Add(result.Rows[i]["TrainID"].ToString());
                }
            }
            List.Insert(0, "");

            return List;
        }

        /// <summary>
        /// 获取白班的出库车
        /// </summary>
        /// <param name="changeZB">早班，白班交接班时间</param>
        /// <param name="bbOutTime">白班开出时间</param>
        /// <param name="lastId">首车次的LastID</param>
        /// <param name="tableName">时刻表名称</param>
        /// <returns></returns>
        public ObservableCollection<string> GetBBFirstOutTrains(string changeZB,int bbOutTime,string lastId,string tableName)
        {
            if (string.IsNullOrEmpty(changeZB) || string.IsNullOrEmpty(tableName))
            {
                return null;
            }
            int int_time_zb = Convert.ToInt32(changeZB);

            ObservableCollection<string> List = new ObservableCollection<string>();
            string sql_getBBFirstOutTrains = "select distinct TrainID from [" + tableName + "] where (Station = 66 or Station = 67 ) and LastID = '" + lastId + "' and Depart between " + int_time_zb + " and " + bbOutTime;
            DataTable result = sqlHelper.SelectDt(sql_getBBFirstOutTrains);
            if (null != result || result.Rows.Count > 0)
            {
                for (int i = 0; i < result.Rows.Count; i++)
                {
                    List.Add(result.Rows[i]["TrainID"].ToString());
                }
            }
            List.Insert(0, "");

            return List;
        }
        public List<String> GetDraftNameList(string tableinfo_id) {
            string sql = "  select distinct draftname from CrossingRoadDraft where tableinfo_id=" + tableinfo_id;
            DataTable dt = sqlHelper.SelectDt(sql);
            List<string> list = new List<string>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(row["draftname"].ToString());
            }
            return list;
        }
        /// <summary>
        /// 该时刻表对应所有车次的集合，包括 车次号，发车时间，到达换班站的时间，结束时间。用于交路表编辑
        /// </summary>
        public ObservableCollection<SmallTrain> GetAllTrain(string tableid,string tableName)
        {
            ObservableCollection<SmallTrain> list = new ObservableCollection<SmallTrain>();

            string fstRoundLstID;
            string lineid = CONSTDATA.UserModel.Line;

            if (CONSTDATA.UserModel.Line == "1")
                fstRoundLstID = "02";
            else
                fstRoundLstID = "01";
            
            string sql_get = "select distinct A.TrainID,A.LastID,B.TBegin,A.Arrive,A.Depart,A.Start,A.Destiny,B.TEnd, DID from [" + tableName + "]" +
                            " A left join TrainDetailInfo B on A.TrainID = B.TrainID  where A.Station = '10' and B.tableInfo_id = '" + tableid + "' order by TBegin";
            DataTable resultDT = sqlHelper.SelectDt(sql_get);
            if (resultDT == null || resultDT.Rows.Count < 1)
                return list;

            for (int i = 0; i < resultDT.Rows.Count; i ++)
            {
                SmallTrain train = new SmallTrain();

                train.TrainID = resultDT.Rows[i]["TrainID"].ToString();
                train.BeginTime = Convert.ToInt32(resultDT.Rows[i]["TBegin"]);
                train.TimeAtChangePoint = Convert.ToInt32(resultDT.Rows[i]["Arrive"].ToString());
                train.TimeLeaveChangePoint = Convert.ToInt32(resultDT.Rows[i]["Depart"].ToString());
                train.EndTime = Convert.ToInt32(resultDT.Rows[i]["TEnd"].ToString());
                train.IsLastTrip = resultDT.Rows[i]["DID"].ToString() == "30" ? true : false;

                int start = Convert.ToInt32(resultDT.Rows[i]["Start"].ToString());
                int destiny = Convert.ToInt32(resultDT.Rows[i]["Destiny"].ToString());

                if (train.TrainID.Substring(3) == fstRoundLstID)
                    train.IsFirstOut = true;

                train.Init(tableName, start, destiny, lineid);

                list.Add(train);
            }

            return list;
        }

        /// <summary>
        /// 检查是否已存在该时刻表对应的交路表
        /// </summary>
        /// <param name="timeTableID">时刻表ID</param>
        /// <returns></returns>
        public bool CheckHasCrossRoad(string timeTableID)
        {
            string sql_check = "select count(*) tableInfo_id from CrossingRoad where tableInfo_id = '" + timeTableID + "' and isused = 1";
            return Convert.ToInt32(sqlHelper.GetSingle(sql_check)) > 0;
        }
        public void DeleteDraft(string timetable, string draftName)
        {
            string sql = "delete from CrossingRoadDraft where tableInfo_id='" + timetable + "' and draftName='" + draftName + "'";
            sqlHelper.ExecuteSql(sql);
        }
        /// <summary>
        /// 返回存在的时刻表对应的交路表
        /// </summary>
        /// <returns></returns>
        public DataTable GetExistCrossingRoad(string timeTableID)
        {
            string sql_get = "select zby,number,grp_type as [type],attenPlace,attenTime,trainTime,reptrain,stringRunTrain,offTrain,bCBackGarageTrainID,midStart,midOut,offTime,offPlace,miles,wHours,notes,lastTrainTime,lastModifiedTime,s.user_view_name as lastModifier,CrName" +
                             " from CrossingRoad left join GroupType on grp_id = GroupType.ID left join [system_user] as s on s.user_id=lastModifier where tableInfo_id = '" + timeTableID + "' and isused = 1 order by zby,number";
            return sqlHelper.SelectDt(sql_get);
        }
        /// <summary>
        /// 返回存在的时刻表对应的交路表
        /// </summary>
        /// <returns></returns>
        public DataTable GetCrossingRoadDraft(string timeTableID,string draftName)
        {
            string sql_get = "select zby,number,grp_type as [type],attenPlace,attenTime,trainTime,reptrain,stringRunTrain,offTrain,bCBackGarageTrainID,midStart,midOut,offTime,offPlace,miles,wHours,notes,lastTrainTime,lastModifiedTime,s.user_view_name as lastModifier,CrName" +
                             " from CrossingRoadDraft left join GroupType on grp_id = GroupType.ID left join [system_user] as s on s.user_id=lastModifier where tableInfo_id = '" + timeTableID + "' and isused = 1 and draftName='" + draftName + "' order by zby,number";
            return sqlHelper.SelectDt(sql_get);
        }

        public bool CheckTrainIDExists(string tableName,string TrainID)
        {
            string sql_check = "select count(*) from [" + tableName + "] where TrainID = '" + TrainID + "'";

            int count = Convert.ToInt32(sqlHelper.GetSingle(sql_check));

            return count > 0;
        }

        /// <summary>
        /// 返回 热备，调车，试车线，备用，正线，特殊组对应的ID
        /// </summary>
        /// <returns></returns>
        public DataTable GetGroupType()
        {
            string sql_get = "select * from GroupType";
            return sqlHelper.SelectDt(sql_get);
        }

        /// <summary>
        /// 删除时刻表对应的交路表,设置isued位为 0 
        /// </summary>
        /// <param name="timeTableID">时刻表</param>
        public bool DeleCrossingRoad(string timeTableID)
        {
            string sql_dele = "update CrossingRoad set isused = 0 where tableInfo_id = '" + timeTableID + "'";
            return sqlHelper.ExecuteSql(sql_dele) > 0;
        }
        public DataTable GetCrossingRoadParameters(string name, string line)
        {
            DataTable dt = sqlHelper.SelectDt("select top 1 * from CrossingRoadParameters where crp_line=" + line + " and crp_table_name='" + name + "' order by crp_last_modified desc");
            return dt;
        }
        public DataTable GetCrossingRoadParamSub(string id)
        {
            DataTable dt = sqlHelper.SelectDt("select  * from CrossingRoadParamSub where crpo_parameters_id='"+id+"'");
            return dt;
        }

        public bool SaveCrossingRoadParameters(CrossingRoadParameter parameters) 
        {
            DataTable dt = GetCrossingRoadParameters(parameters.TableName, parameters.lineID);

            string sql = "";
            string id = Guid.NewGuid().ToString();
            if (dt.Rows.Count > 0)
            {
                sql += "delete from CrossingRoadParameters where id='"+dt.Rows[0]["id"]+"';";
                sql += "delete from CrossingRoadParamSub where crpo_parameters_id='" + dt.Rows[0]["id"]+"';";
            }
            //else
            //{
            //    sql += "update CrossingRoadParameters set ";
            //}
            sql += "insert into CrossingRoadParameters values('" + id + "','" + parameters.PhoneOutTrain + "','" + parameters.NightEmptyTrain +
    "'," + parameters.lunchCrossRoadCount + "," + parameters.supperCrossRoadCount + "," +
    Convert.ToInt32(parameters.Is_NightMeal_BC) + "," + parameters.int_ChangeTime_ZB + "," + parameters.int_ChangeTime_BB + "," +
    parameters.int_ZB_OutTime + "," + parameters.int_ZB_BackTime + "," + parameters.int_BB_OutTime + "," +
    parameters.int_LunchTime + "," + parameters.int_SuperTime + "," + parameters.restTime + "," + parameters.mealRestTime + "," +
    parameters.GarageAhead + "," + parameters.GarageBCAhead + "," + parameters.NormalAhead + "," +
    parameters.lineID + ",'" + parameters.TableName + "',getdate()," + parameters.NormalDelay + "," + parameters.GarageDelay + "," + parameters.BcGarageDelay;
            sql += ");";

            string subFormat = "insert into CrossingRoadParamSub(id,crpo_parameters_id,crpo_tag,crpo_class,crpo_train,crpo_station,crpo_number,crpo_is_biancheng,crpo_is_continuous_duty) values({0},'" + id + "',{1},{2},{3},{4},{5},{6},{7});";
            for (int i = 0; i < parameters.bc_last_arr.Count;i++ )
            {

                string subid = Guid.NewGuid().ToString();
                ParametersSubType subtype=ParametersSubType.bc_last;
                var item = parameters.bc_last_arr[i];

                sql += string.Format(subFormat,
                    "'" + subid + "'",
                    "'" + subtype.ToString() + "'",
                    "NULL",
                    "'" + item + "'",
                    "NULL",
                    "NULL","NULL","NULL"
                    );
            }
            for (int i = 0; i < parameters.bc_first_arr.Count; i++)
            {

                string subid = Guid.NewGuid().ToString();
                ParametersSubType subtype = ParametersSubType.bc_first;
                var item = parameters.bc_first_arr[i];
                sql += string.Format(subFormat,
                    "'" + subid + "'",
                    "'" + subtype.ToString() + "'",
                    "NULL",
                    "'" + item + "'",
                    "NULL",
                    "NULL", "NULL", "NULL"
                    );
            }
            foreach (var key in parameters.UnMorningBcCollection.Keys)
            {
                string subid = Guid.NewGuid().ToString();
                ParametersSubType subtype = ParametersSubType.unmorning_bc;
                ClassType banci = parameters.UnMorningBcCollection[key];
                string classText = null;
                switch (banci)
                {
                    case ClassType.Day:
                        classText = "白班";
                        break;
                    case ClassType.Night:
                        classText = "夜班";
                        break;
                    default:
                        break;
                }
                if(classText==null)
                    break;
                sql += string.Format(subFormat,
                    "'" + subid + "'",
                    "'" + subtype.ToString() + "'",
                    "'"+classText+"'",
                    "'"+key+"'",
                    "NULL",
                    "NULL", "NULL", "NULL"
                    );
            }
            for (int i = 0; i < parameters.UnNightBCOutCollection.Count; i++)
            {

                string subid = Guid.NewGuid().ToString();
                ParametersSubType subtype = ParametersSubType.unnight_bc_out;
                var item = parameters.UnNightBCOutCollection[i];
                sql += string.Format(subFormat,
                    "'" + subid + "'",
                    "'" + subtype.ToString() + "'",
                    "NULL",
                    "'" + item + "'",
                    "NULL",
                    "NULL", "NULL", "NULL"
                    );
            }
            foreach (var item in parameters.EarlyBackGarageCollections)
            {
                string subid = Guid.NewGuid().ToString();
                ParametersSubType subtype = ParametersSubType.early_back_garage;
                sql += string.Format(subFormat,
                    "'" + subid + "'",
                    "'" + subtype.ToString() + "'",
                    "NULL",
                    "'" + item.TrainID + "'",
                    "'"+item.StationSelected.StationName+"'",
                    item.Num,"NULL","NULL"
                    );
            }
            foreach (var item in parameters.SetMealCrossRoadCollection)
            {
                string subid = Guid.NewGuid().ToString();
                ParametersSubType subtype = ParametersSubType.meal_cross_road;
                sql += string.Format(subFormat,
                    "'" + subid + "'",
                    "'" + subtype.ToString() + "'",
                    "'"+item.MealTime+"'",
                    "NULL",
                    "NULL",
                    "NULL", Convert.ToInt32(item.Is_NightMeal_BC), Convert.ToUInt32(item.Is_Continue)
                    );
            }

            if (sqlHelper.ExecuteSql(sql) > 0)
                return true;
            return false;
        }
        /// <summary>
        /// 保存交路表的信息
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public bool SaveNewCrossingRoad(DataTable dt,string draftName)
        {
            Helpers.SYCWPBGlobalvariables.globalvariables.log.writeInfo("保持的交路表信息");
            bool result = false;

            try
            {
                using (SqlBulkCopy bcp = new SqlBulkCopy(SqlDataLayer.ConnStr))
                {
                    bcp.BatchSize = 100;
                    if (string.IsNullOrEmpty(draftName))
                    {
                        bcp.DestinationTableName = "CrossingRoad";
                    }
                    else
                    {
                        bcp.DestinationTableName = "CrossingRoadDraft";
                    }
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        //bcp.ColumnMappings.Add(i, (i + 1) );
                        bcp.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                    }
                    bcp.WriteToServer(dt);

                    result = true;
                }
            }
            catch (System.Exception ex)
            {
                string err = ex.Message;
                Helpers.SYCWPBGlobalvariables.globalvariables.log.writeInfo(ex.Message);
            }

            return result;
        }
    }
}
